﻿CREATE TRIGGER [dbo].[Vanzare_Arhiva_Trigger]
ON [dbo].[Vanzare]
AFTER INSERT
AS
   DECLARE @id_Medicament int 
   DECLARE @cantitate float
   DECLARE @data datetime
   DECLARE @cantitate_per_stoc float
   DECLARE @id_stoc int
   
   
   DECLARE @medicamente_inserate TABLE
   (
		Id_Medicament int,
		Cantitate float,
		Data datetime,   
		Checked bit DEFAULT(0)
  )
  
  INSERT INTO @medicamente_inserate(Id_Medicament,Cantitate,Data)
  SELECT Id_Medicament,Cantitate,Data
  FROM inserted

  SELECT TOP 1
		  @id_Medicament = ID_Medicament,
		  @cantitate = Cantitate,
		  @data = Data
  FROM @medicamente_inserate
  WHERE Checked = 0
	
  WHILE @id_Medicament IS NOT NULL
  BEGIN
	SELECT TOP 1
		  @id_Medicament = ID_Medicament,
		  @cantitate = Cantitate,
		  @data = Data
	FROM @medicamente_inserate
	
	UPDATE TOP(1) @medicamente_inserate
	SET Checked = 1 
	WHERE Id_Medicament = @id_Medicament AND Checked = 0
	
	IF EXISTS(SELECT 
				 TOP 1 * 
			  FROM 
				 Arhiva 
			  WHERE
				 Id_Medicament = @id_Medicament AND 
				 DATEDIFF(day,Data,@data) = 0
			  )	
	BEGIN
		UPDATE Arhiva
		SET Cantitate = Cantitate + @cantitate
		WHERE Id_Medicament = @id_Medicament
			AND DATEDIFF(day,Data,@data) = 0
	END
	ELSE
	BEGIN
		INSERT INTO Arhiva(Id_Medicament,Cantitate,Data)
		VALUES (@id_Medicament,@cantitate,@data)
	END
	
	WHILE @cantitate > 0
	BEGIN
		SELECT TOP 1 @id_stoc = Id_Stoc,@cantitate_per_stoc = Cantitate_Actuala
		FROM STOC
		WHERE Id_Medicament = @id_Medicament AND
			  Cantitate_Actuala > 0
		ORDER BY Data_Expirare ASC
		
		IF @cantitate_per_stoc > @cantitate
		BEGIN
			UPDATE Stoc 
			SET Cantitate_Actuala = Cantitate_Actuala - @cantitate
			WHERE Id_Stoc = @id_stoc
			
			SET @cantitate = 0
		END
		ELSE
		BEGIN
			UPDATE Stoc
			SET Cantitate_Actuala = 0
			WHERE Id_Stoc = @id_stoc
			
			SET @cantitate = @cantitate - @cantitate_per_stoc
		END
	END
	
	SET @id_Medicament = NULL
	
	SELECT TOP 1
		  @id_Medicament = ID_Medicament,
		  @cantitate = Cantitate,
		  @data = Data
	FROM @medicamente_inserate
	WHERE Checked = 0
  END

